package entity.connector;

import entity.bean.PurchaseRec;
import entity.bean.Ticket;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 *
 * @author 52571783
 */
public class PurchaseUploader {

    private static PurchaseUploader instance = null;
    private int purchaseID;

    private PurchaseUploader() {
        try {
            Connection con = ConnectionProvider.getCon();
            // store purchase record into database
            PreparedStatement ps = con.prepareStatement(
                    "INSERT INTO purchase (purchased_tickets) VALUES (0);");
            ps.executeUpdate();
            // get the generated ID from database
            ps = con.prepareStatement(
                    "SELECT purchase_id FROM purchase ORDER BY purchase_id DESC",
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = ps.executeQuery();
            // store the generated ID from result set to static data members
            rs.next();
            purchaseID = rs.getInt("purchase_id");
            // close resources after use
            rs.close();
            ps.close();
        } catch (Exception e) {
            purchaseID = 0;
        }
    }

    // return the same PurchaseUploader within the same session
    public static PurchaseUploader getInstance() {
        if (instance == null || instance.timeOut()) {
            instance = new PurchaseUploader();
        }
        return instance;
    }

    // throw away the PurchaseUploader for the last session
    private static void throwInstance() {
        instance = null;
    }

    // store purchased ticket into database
    public void purchaseTicket(Ticket ticket) {
        try {
            Connection con = ConnectionProvider.getCon();
            // store purchased ticket into database
            PreparedStatement ps = con.prepareStatement(
                    "INSERT INTO movie_ticket (movie_id,movie_time,row_no,col_no,purchase_id) VALUES (?,?,?,?,?)");
            ps.setInt(1, ticket.getMovieID());
            ps.setString(2, TimeConvertor.calendarToString(ticket.getTime()));
            ps.setInt(3, ticket.getRowNo());
            ps.setInt(4, ticket.getColNo());
            ps.setInt(5, purchaseID);
            ps.executeUpdate();
            ps = con.prepareStatement(
                    "UPDATE purchase SET purchase_time = GETDATE() WHERE purchase_id = ?");
            ps.setInt(1, purchaseID);
            ps.executeUpdate();
            // close resources after use
            ps.close();
        } catch (Exception e) {
        }
    }

    // confirm purchased ticket in database
    public void confirmTicket(Ticket ticket) {
        try {
            Connection con = ConnectionProvider.getCon();
            // store purchase record into database
            PreparedStatement ps = con.prepareStatement(
                    "UPDATE movie_ticket SET price = ?, discount = ? "
                    + "WHERE purchase_id = ? AND movie_id = ? AND movie_time = ? AND row_no = ? AND col_no = ?");
            ps.setInt(1, ticket.getPrice());
            ps.setBoolean(2, ticket.getDiscount());
            ps.setInt(3, purchaseID);
            ps.setInt(4, ticket.getMovieID());
            ps.setString(5, TimeConvertor.calendarToString(ticket.getTime()));
            ps.setInt(6, ticket.getRowNo());
            ps.setInt(7, ticket.getColNo());
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
        }
    }

    // confirm purchase record in database
    public void confirmPurchase(PurchaseRec purchase) {
        try {
            Connection con = ConnectionProvider.getCon();
            // confirm purchase record in database
            PreparedStatement ps = con.prepareStatement(
                    "UPDATE purchase SET purchase_time = GETDATE(), purchased_tickets = ?, "
                    + "purchase_price = ?, user_id = ?, spent_points = ? WHERE purchase_id = ?");
            ps.setInt(1, purchase.getNoOfTickets());
            ps.setInt(2, purchase.getPrice());
            ps.setString(3, purchase.getUserID());
            ps.setInt(4, purchase.getPoints());
            ps.setInt(5, purchaseID);
            ps.executeUpdate();
            // update loyalty points
            if (purchase.getPoints() > 0) {
                ps = con.prepareStatement("UPDATE users SET points = (points - ?) WHERE user_id = ?");
                ps.setInt(1, purchase.getPoints());
                ps.setString(2, purchase.getUserID());
                ps.executeUpdate();
            }
            // delete unpurchased tickets in database
            ps = con.prepareStatement("DELETE FROM movie_ticket WHERE purchase_id = ? AND price IS NULL");
            ps.setInt(1, purchaseID);
            ps.executeUpdate();
            ps.close();
            throwInstance();
        } catch (Exception e) {
        }
    }

    public boolean timeOut() {
        try {
            Connection con = ConnectionProvider.getCon();
            PreparedStatement ps = con.prepareStatement(
                    "SELECT * FROM purchase WHERE purchase_id = ? AND DATEDIFF(second,purchase_time,GETDATE()) < 300");
            ps.setInt(1, purchaseID);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                rs.close();
                ps.close();
                return false;
            }
            ps = con.prepareStatement("DELETE FROM purchase WHERE purchase_id = ?");
            ps.setInt(1, purchaseID);
            ps.executeUpdate();
            ps = con.prepareStatement("DELETE FROM movie_ticket WHERE purchase_id = ?");
            ps.setInt(1, purchaseID);
            ps.executeUpdate();
            ps.close();
            throwInstance();
            return true;
        } catch (Exception e) {
            return false;
        }
    }
}